<?php
class ModelProductSelect extends Model {

	//-------------------------------------------------------------------------//
	//Check duplicate couple product id and catalog id
	//-------------------------------------------------------------------------//
	public function checkDuplicatePC($product_id,$category_id) {
		$SQL = " SELECT COUNT(1) as total FROM product_to_category ";
		$SQL .= "  WHERE product_id ='".$product_id."'";
		$SQL .= "    AND category_id = '".$category_id."'";
		$query = $this->db->query($SQL);
		return $query->row['total'];

	}

	//-------------------------------------------------------------------------//
	// Get list category base on product_id
	//-------------------------------------------------------------------------//
	public function getListCategories($product_id) {
		$SQL = " SELECT category_id FROM product_to_category ";
		$SQL .= "  WHERE product_id ='".$product_id."'";
		$SQL .= "  ORDER BY category_id ASC ";
		$query = $this->db->query($SQL);
		$str = "";
		if(empty($query->rows) == false){
			foreach($query->rows as $data){
				$str .= "_".$data["category_id"];
			}
		}
		return substr($str,1);
	}

	//-------------------------------------------------------------------------//
	// Get product by Product Id
	//-------------------------------------------------------------------------//
	public function getProductsByProductId($productId, $sort = 'pd.name',
									$order = 'ASC', $start = 0,
									$limit = 1) {
		$SQL = "SELECT *, ";
		$SQL .=	"SUBSTRING( pd.name,1, 20) AS name, ";
		$SQL .=		"p.image, m.name AS manufacturer, ";
		$SQL .=		"shop.customer_id AS customer_id, ";
		$SQL .=		"ss.name AS stock,p.payment_id as payment_id, ";
		$SQL .=		"(SELECT AVG(r.rating) FROM " . DB_PREFIX . "review r ";
		$SQL .=				"WHERE p.product_id = r.product_id ";
		$SQL .=				"GROUP BY r.product_id) AS rating ";
		$SQL .=		" FROM " . DB_PREFIX . "product p ";
		$SQL .=			"LEFT JOIN " . DB_PREFIX . "product_description pd ";
		$SQL .=				"ON (p.product_id = pd.product_id) ";
		$SQL .=			"LEFT JOIN " . DB_PREFIX . "manufacturer m ";
		$SQL .=				"ON (p.manufacturer_id = m.manufacturer_id) ";
		$SQL .=			"LEFT JOIN " . DB_PREFIX . "stock_status ss ";
		$SQL .=				"ON (p.stock_status_id = ss.stock_status_id) ";
		$SQL .=			"LEFT JOIN " . DB_PREFIX . "product_to_category p2c ";
		$SQL .=				"ON (p.product_id = p2c.product_id) ";

		$SQL .=			"LEFT JOIN " . DB_PREFIX . "shops shop ";
		$SQL .=				"ON (shop.shop_id = p.shop_id) ";
		$SQL .=		" WHERE p.status = '1' AND p.date_available <= NOW() ";
		$SQL .=		" AND ss.language_id = '" . (int)$this->language->getId()."'";
		if(mb_strlen($productId) > 0 ){
				$SQL .=	 " AND p.product_id = '" .(int)$productId."'";
		}
		$SQL .= " GROUP BY p.product_id ";
		$SQL .= " LIMIT " . (int)$start . "," . (int)$limit;
		$query = $this->db->query($SQL);
		return $query->rows;
	}

	//-------------------------------------------------------------------------//
	// Get product by Product Id for update screen
	//-------------------------------------------------------------------------//
	public function getProductsByProductId1($productId){
		$SQL = "SELECT p.price, p.image, p.payment_id, p.shop_id, p.user_flag, ";
		$SQL .=	" p.sale_flg, p.zone_id, p.description, p.name AS name, ";
		$SQL .=		"shop.customer_id AS customer_id, p2c.category_id, ";
		$SQL .=		" p.payment_id as payment_id ";
		$SQL .=		" FROM " . DB_PREFIX . "product p ";
		//$SQL .=			"LEFT JOIN " . DB_PREFIX . "product_description pd ";
		//$SQL .=				"ON (p.product_id = pd.product_id) ";
		$SQL .=			"LEFT JOIN " . DB_PREFIX . "product_to_category p2c ";
		$SQL .=				"ON (p.product_id = p2c.product_id) ";
		$SQL .=			"LEFT JOIN " . DB_PREFIX . "shops shop ";
		$SQL .=				"ON (shop.shop_id = p.shop_id) ";
		$SQL .=		" WHERE p.status = '1' AND p.date_available <= NOW() ";
		$SQL .=	 " AND p.product_id = '" .(int)$productId."'";
		$SQL .= " LIMIT 0, 1 ";
		$query = $this->db->query($SQL);
		return $query->rows;
	}
}
?>